package StudentProject.dao;

import StudentProject.bean.Student;
import StudentProject.jdbc.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Scanner;

public class StudentDaoImp implements StudentDao{

    //sql语句
    private static final String SQL_USER_INSET = "INSERT INTO students VALUES(student_id,?,?,?)";
    private static final String SQL_USER_DELETE_id = "DELETE from students where student_id = ?";
    private static final String SQL_USER_DELETE_name = "DELETE from students where name = ?";
    private static final String SQL_USER_UPDATE = "update students set name = ?,age = ?,gender =? where student_id = ?";
    private static final String SQL_USER_SELECT_ID = "SELECT * FROM students WHERE student_id = ?";
    private static final String SQL_USER_SELECT_ALL = "SELECT * FROM students";






    @Override
    public boolean insert(Student student) {
        //连接数据库，创建连接对象conn
        Connection conn = JDBCUtils.getConnection();
        //创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
             preparedStatement = conn.prepareStatement(SQL_USER_INSET);
             //设置sql语句参数
            preparedStatement.setString(1,student.getName());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.setString(3,student.getGender());
            //运行sql
            int line = preparedStatement.executeUpdate();
            //判断结果返回true或false
            return line>0? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    //按照id进行删除学生信息
    @Override
    public boolean deleteId(int student_id) {
        //连接数据库，创建连接对象conn
        Connection conn = JDBCUtils.getConnection();
        //创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(SQL_USER_DELETE_id);
            //设置sql语句参数
            preparedStatement.setInt(1,student_id);
            //运行sql
            int line = preparedStatement.executeUpdate();
            //判断结果返回true或false
            return line>0? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    //按照姓名进行删除学生信息
    @Override
    public boolean deleteName(String name) {
        //连接数据库，创建连接对象conn
        Connection conn = JDBCUtils.getConnection();
        //创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(SQL_USER_DELETE_name);
            //设置sql语句参数
            preparedStatement.setString(1,name);
            //运行sql
            int line = preparedStatement.executeUpdate();
            //判断结果返回true或false
            return line>0? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }



    //修改学生信息
    @Override
    public boolean update(Student student) {
        //连接数据库，创建连接对象conn
        Connection conn = JDBCUtils.getConnection();
        //创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(SQL_USER_UPDATE);
            //设置sql语句参数
            preparedStatement.setString(1,student.getName());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.setString(3,student.getGender());
            preparedStatement.setInt(4,student.getStudent_id());
            //运行sql
            int line = preparedStatement.executeUpdate();
            //判断结果返回true或false
            return line>0? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    //查询单个学生信息id
    @Override
    public Student selectId(int student_id) {
        //连接数据库，创建conn对象
        Connection conn = JDBCUtils.getConnection();
        //创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(SQL_USER_SELECT_ID);
            //设置sql参数
            preparedStatement.setInt(1,student_id);
            //运行sql语句
            ResultSet resultSet = preparedStatement.executeQuery();
            //循环结果集合
            while (resultSet.next()){
                student_id = resultSet.getInt("student_id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                return new Student(student_id,name,age,gender);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    //查询单个学生信息姓名
    @Override
    public Student selectAll(String name) {
        //连接数据库，创建conn对象
        Connection conn = JDBCUtils.getConnection();
        //创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(SQL_USER_SELECT_ALL);
            //设置sql参数
            //运行sql语句
            ResultSet resultSet = preparedStatement.executeQuery();
            //循环结果集合
            while (resultSet.next()){
                int student_id = resultSet.getInt("student_id");
                name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                return new Student(student_id,name,age,gender);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    //查询多个学生信息
    @Override
    public Student select() {
        //连接数据库，创建conn对象
        Connection conn = JDBCUtils.getConnection();
        //创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(SQL_USER_SELECT_ALL);
            //设置sql参数
            //运行sql语句
            ResultSet resultSet = preparedStatement.executeQuery();
            //创建对象
            List<Student> stu = new ArrayList<>();
            List<String> stus = new ArrayList<>();
            Student student = null;
            //循环结果集合
            while (resultSet.next()){
                int student_id = resultSet.getInt("student_id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");

                student = new Student();
                student.setStudent_id(student_id);
                student.setName(name);
                student.setAge(age);
                student.setGender(gender);
                String studentok = "学生id = " + student.getStudent_id() + ",学生姓名 = " + student.getName() + ",学生性别 = " + student.getGender() + ",学生年龄 = " + student.getAge();
                stus.add(studentok);
                stu.add(student);
            }
            System.out.println("以下为所有学生信息：");
            for (String i :stus) {
                System.out.println(i);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //分页查询
    public  List<Student> selectStudents(int pageNumber, int pageSize) {
        // 连接数据库，创建 conn 对象
        Connection conn = JDBCUtils.getConnection();
        // 创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
            String sql = SQL_USER_SELECT_ALL + " LIMIT " + (pageNumber - 1) * pageSize + ", " + pageSize;
            preparedStatement = conn.prepareStatement(sql);
            // 运行 SQL 语句
            ResultSet resultSet = preparedStatement.executeQuery();
            // 创建对象
            List<Student> stu = new ArrayList<>();
            // 循环结果集合
            while (resultSet.next()) {
                int student_id = resultSet.getInt("student_id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");

                Student student = new Student();
                student.setStudent_id(student_id);
                student.setName(name);
                student.setAge(age);
                student.setGender(gender);

                stu.add(student);
            }
            return stu;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (preparedStatement!= null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    //排序查询
    public Student select(int choice, int orderChoice){
        // 连接数据库，创建 conn 对象
        Connection conn = JDBCUtils.getConnection();
        // 创建预编译环境
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(SQL_USER_SELECT_ALL);
            // 运行 SQL 语句
            ResultSet resultSet = preparedStatement.executeQuery();
            // 创建对象
            List<Student> stu = new ArrayList<>();
            List<String> stus = new ArrayList<>();
            Student student = null;
            // 循环结果集合
            while (resultSet.next()) {
                int student_id = resultSet.getInt("student_id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");

                student = new Student();
                student.setStudent_id(student_id);
                student.setName(name);
                student.setAge(age);
                student.setGender(gender);
                String studentok = "学生id = " + student.getStudent_id() + ",学生姓名 = " + student.getName() + ",学生性别 = " + student.getGender() + ",学生年龄 = " + student.getAge();
                stus.add(studentok);
                stu.add(student);
            }

            System.out.println("以下为所有学生信息：");
            if (choice == 1) {  // 按照 ID 排序
                if (orderChoice == 1) {  // 升序
                    //Comparator.comparingInt对Student里面的id进行比较，然后进行升序。末尾加上翻转进行降序
                    stu.sort(Comparator.comparingInt(Student::getStudent_id));
                } else {  // 降序
                    stu.sort(Comparator.comparingInt(Student::getStudent_id).reversed());
                }
            } else if (choice == 2) {  // 按照姓名排序
                if (orderChoice == 1) {  // 升序
                    stu.sort(Comparator.comparing(Student::getName));
                } else {  // 降序
                    stu.sort(Comparator.comparing(Student::getName).reversed());
                }
            } else if (choice == 3) {  // 按照年龄排序
                if (orderChoice == 1) {  // 升序
                    stu.sort(Comparator.comparingInt(Student::getAge));
                } else {  // 降序
                    stu.sort(Comparator.comparingInt(Student::getAge).reversed());
                }
            }

            for (Student s : stu) {
                System.out.println("学生id = " + s.getStudent_id() + ", 学生姓名 = " + s.getName() + ", 学生性别 = " + s.getGender() + ", 学生年龄 = " + s.getAge());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    //成绩查询
    @Override
    public void queryGrade(Scanner scanner) {
        System.out.println("请输入学号：");
        int studentId = scanner.nextInt();

        try (Connection connection =  JDBCUtils.getConnection();
             PreparedStatement statement = connection.prepareStatement("SELECT g.* FROM grades g JOIN students s ON g.student_id = s.student_id WHERE s.student_id =?")) {

            statement.setInt(1, studentId);

            ResultSet resultSet = statement.executeQuery();

            System.out.println("该学生的成绩如下：");
            while (resultSet.next()) {
                String courseCode = resultSet.getString("course_code");
                double score = resultSet.getDouble("score");
                System.out.println("课程代码：" + courseCode + "，成绩：" + score);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



